<% title "Service Information Tables" %> 
<% idFileRedBee = SelectFile.where(:server => "RedBee").first.id%>
<% idSIT = ServiceInformationTable.where(:select_file_id => idFileRedBee).first.id %>

<%	logger.debug " load RedBee info"

	resultAVAttributes = GroupInformationTable.find_by_sql("\
	SELECT service_informations.serviceId, service_informations.name, \
	sum(case when avattributes.avtype='video' then 1 else 0 end) as video, \
	sum(case when avattributes.avtype='audio' then 1 else 0 end) as audio, count(avattributes.id) as total,\
	schedules.start, schedules.end\
	FROM program_informations, schedule_events, schedules, service_informations, avattributes \
	WHERE schedule_events.schedule_id=schedules.id \
	AND schedule_events.programCrid=program_informations.crid AND schedule_events.programCrid=program_informations.crid \
	AND service_informations.serviceId=schedules.service_id \
	AND program_informations.program_information_table_id=1 AND schedules.program_location_table_id=1 \
	AND service_informations.service_information_table_id=1\
	AND avattributes.schedule_event_id=schedule_events.id \
	GROUP BY service_informations.serviceId ORDER BY service_informations.serviceId")
	
	resultAVAttributesHash = Hash[resultAVAttributes.map{|i| [i['name'],[i['serviceId'],i['video'],i['audio'],i['start'],i['end'],i['total']]]}]
	
	resultLanguage = GroupInformationTable.find_by_sql("\
		SELECT serviceId, name, language, MAX(items) as items FROM\
		(\
			SELECT service_informations.serviceId, service_informations.name, program_informations.language, \
			count(program_informations.id) AS items FROM program_informations, schedule_events, schedules, service_informations \
			WHERE schedule_events.schedule_id=schedules.id AND schedule_events.programCrid=program_informations.crid \
			AND schedule_events.programCrid=program_informations.crid AND service_informations.serviceId=schedules.service_id \
			AND program_informations.program_information_table_id=1 AND schedules.program_location_table_id=1 \
			AND service_informations.service_information_table_id=1 \
			GROUP BY service_informations.serviceId, program_informations.language \
			ORDER BY service_informations.serviceId, \
			COUNT(program_informations.language) DESC\
		) as counts group by serviceId\
	")
	resultLanguageHash = Hash[resultLanguage.map{|i| [i['name'],[i['serviceId'],i['language'],i['items']]]}]
	
	resultTotalProgramInformationItems = GroupInformationTable.find_by_sql("\
		SELECT service_informations.name, count(program_informations.id) as items, schedules.start, schedules.end\
		FROM program_informations, schedule_events, schedules, service_informations \
		WHERE schedule_events.schedule_id=schedules.id \
		AND schedule_events.programCrid=program_informations.crid AND schedule_events.programCrid=program_informations.crid \
		AND service_informations.serviceId=schedules.service_id \
		AND program_informations.program_information_table_id=1 AND schedules.program_location_table_id=1 \
		AND service_informations.service_information_table_id=1\
		GROUP BY service_informations.serviceId\
		")
	resultTotalProgramInformationItemsHash = Hash[resultTotalProgramInformationItems.map{|i| [i['name'],[i['items'],i['start'],i['end']]]}]
%>

<%	logger.debug " load AxelS info"

	resultAVAttributesAxelS = GroupInformationTable.find_by_sql("\
	SELECT service_informations.serviceId, service_informations.name, \
	sum(case when avattributes.avtype='video' then 1 else 0 end) as video, \
	sum(case when avattributes.avtype='audio' then 1 else 0 end) as audio, count(avattributes.id) as total,\
	schedules.start, schedules.end\
	FROM program_informations, schedule_events, schedules, service_informations, avattributes \
	WHERE schedule_events.schedule_id=schedules.id \
	AND schedule_events.programCrid=program_informations.crid \
	AND service_informations.serviceId=schedules.service_id \
	AND program_informations.program_information_table_id!=1 \
	AND schedules.program_location_table_id!=1 \
	AND service_informations.service_information_table_id!=1\
	AND avattributes.schedule_event_id=schedule_events.id \
	GROUP BY service_informations.serviceId ORDER BY service_informations.serviceId")
	
	resultAVAttributesAxelSHash = Hash[resultAVAttributesAxelS.map{|i| [i['name'],[i['serviceId'],i['video'],i['audio'],i['start'],i['end'],i['total']]]}]
	
	resultLanguageAxelS = GroupInformationTable.find_by_sql("\
		SELECT serviceId, name, language, MAX(items) as items FROM\
		(\
			SELECT service_informations.serviceId, service_informations.name, program_informations.language, \
			count(DISTINCT program_informations.crid) AS items FROM program_informations, schedule_events, schedules, service_informations \
			WHERE schedule_events.schedule_id=schedules.id \
			AND schedule_events.programCrid=program_informations.crid \
			AND service_informations.serviceId=schedules.service_id \
			AND program_informations.program_information_table_id!=1 \
			AND schedules.program_location_table_id!=1 \
			AND service_informations.service_information_table_id!=1 \
			GROUP BY service_informations.serviceId, program_informations.language \
			ORDER BY service_informations.serviceId, \
			COUNT(program_informations.language) DESC\
		) as counts group by serviceId\
	")
	resultLanguageAxelSHash = Hash[resultLanguageAxelS.map{|i| [i['name'],[i['serviceId'],i['language'],i['items']]]}]
	
	resultTotalProgramInformationItemsAxelS = GroupInformationTable.find_by_sql("\
		SELECT service_informations.name, count(DISTINCT program_informations.crid) as items, schedules.start, schedules.end\
		FROM program_informations, schedule_events, schedules, service_informations \
		WHERE schedule_events.schedule_id=schedules.id \
		AND schedules.service_id=service_informations.serviceId\
		AND schedule_events.programCrid=program_informations.crid \
		AND service_informations.serviceId=schedules.service_id \
		AND program_informations.program_information_table_id!=1 AND schedules.program_location_table_id!=1 \
		AND service_informations.service_information_table_id!=1\
		GROUP BY service_informations.serviceId\
		")
	resultTotalProgramInformationItemsAxelSHash = Hash[resultTotalProgramInformationItemsAxelS.map{|i| [i['name'],[i['items'],i['start'],i['end']]]}]
%>
<p>
  Download:
  <%= link_to "CSV", service_information_tables_path(format: "csv") %> |
  <%= link_to "Excel", service_information_tables_path(format: "xls") %>
</p>


<table class='tabla'>
	<tr  class='modo1'>
    	<th colspan="2">Default channels (<%= DefaultChannel.count%> services)</th>
    	<th colspan="6">RedBee (<%= ServiceInformation.where(:service_information_table_id=>idSIT).count %> services)</th>
    	<th colspan="7">AxelS (<%= ServiceInformation.select{|i| i.service_information_table_id != 1}.map{|i| i.name}.uniq.count%> services)</th>
  </tr>
  <tr  class='modo1'>
    <th>Name</th>
    <th>Expected Language</th>
    <th>Name</th>
    <th>Language</th>
    <th>Audio attr</th>
    <th>Video attr</th>
    <th>Start</th>
    <th>End</th>
    <th>Name</th>
    <th>Language</th>
    <th>Audio attr</th>
    <th>Video attr</th>
    <th>Start</th>
    <th>End</th>
    <th>Files</th>
  </tr>
  
  <% for default_channel in DefaultChannel.all %>
    <tr  class='modo2'>
      	<td><%= default_channel.name %></td>
      	<td><%= default_channel.language %></td>
      	<% if resultLanguageHash.has_key?(default_channel.name) %>
	      	<% if resultTotalProgramInformationItemsHash.has_key?(default_channel.name) %>
	      		<td><%= default_channel.name %> with <%= resultTotalProgramInformationItemsHash[default_channel.name][0].to_s %> items</td>
		  		<td><%= resultLanguageHash[default_channel.name][2] %> in <%= resultLanguageHash[default_channel.name][1].to_s%></td>
		  	<%else%>
		  		<td> - </td>
		  		<td> - </td>
		  	<%end%>
		  	<% if resultAVAttributesHash.has_key?(default_channel.name) %>
		  		<td><%= resultAVAttributesHash[default_channel.name][2].to_s %></td>
		  		<td><%= resultAVAttributesHash[default_channel.name][1].to_s %></td>
		  	<%else%>
			  	<td> - </td>
			  	<td> - </td>
		  	<%end%>
		  	<td><%= resultTotalProgramInformationItemsHash[default_channel.name][1].to_s.first(10)  %></td>
		  	<td><%= resultTotalProgramInformationItemsHash[default_channel.name][2].to_s.first(10)  %></td>
		<% else%>
			<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
	  	<%end%>
	  	<% if resultLanguageAxelSHash.has_key?(default_channel.name) %>
	      	<% if resultTotalProgramInformationItemsAxelSHash.has_key?(default_channel.name) %>
	      		<td><%= default_channel.name %> with <%= resultTotalProgramInformationItemsAxelSHash[default_channel.name][0].to_s %> items</td>
		  		<td><%= resultLanguageAxelSHash[default_channel.name][2] %> in <%= resultLanguageAxelSHash[default_channel.name][1].to_s%></td>
		  	<%else%>
		  		<td> - </td>
		  		<td> - </td>
		  	<%end%>
		  	<% if resultAVAttributesAxelSHash.has_key?(default_channel.name) %>
		  		<td><%= resultAVAttributesAxelSHash[default_channel.name][2].to_s %></td>
		  		<td><%= resultAVAttributesAxelSHash[default_channel.name][1].to_s %></td>
		  	<%else%>
			  	<td> - </td>
			  	<td> - </td>
		  	<%end%>
		  	<td><%= resultTotalProgramInformationItemsAxelSHash[default_channel.name][1].to_s.first(10)  %></td>
		  	<td><%= resultTotalProgramInformationItemsAxelSHash[default_channel.name][2].to_s.first(10)  %></td>
	  		<td> - </td>
		<% else%>
			<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
	  		<td> - </td>
	  	<%end%>
	 </tr> 	
	 <% resultTotalProgramInformationItemsHash.delete(default_channel.name)%>
	 <% resultTotalProgramInformationItemsAxelSHash.delete(default_channel.name)%>
   <% end %>
   <% for item in resultTotalProgramInformationItemsHash %>
   <tr  class='modo2'>
      	<td> - </td>
      	<td> - </td>
      	<% if resultTotalProgramInformationItemsHash.has_key?(item[0]) %>
      		<td><%= item[0] %> with <%= item[1][0].to_s %> items</td>
	  		<td><%= resultLanguageHash[item[0]][2] %> in <%= resultLanguageHash[item[0]][1].to_s%></td>
	  	<%else%>
	  		<td> - </td>
	  		<td> - </td>
	  	<%end%>
	  	<% if resultAVAttributesHash.has_key?(item[0]) %>
	  		<td><%= resultAVAttributesHash[item[0]][2].to_s %></td>
	  		<td><%= resultAVAttributesHash[item[0]][1].to_s %></td>
	  	<%else%>
		  	<td> - </td>
		  	<td> - </td>
	  	<%end%>
	  	<td><%= item[1][1].to_s.first(10)  %></td>
	  	<td><%= item[1][2].to_s.first(10)  %></td>
	<% if resultLanguageAxelSHash.has_key?(item[0]) %>
	      	<% if resultTotalProgramInformationItemsAxelSHash.has_key?(item[0]) %>
	      		<td><%= default_channel.name %> with <%= resultTotalProgramInformationItemsAxelSHash[item[0]][0].to_s %> items</td>
		  		<td><%= resultLanguageAxelSHash[item[0]][2] %> in <%= resultLanguageAxelSHash[item[0]][1].to_s%></td>
		  	<%else%>
		  		<td> - </td>
		  		<td> - </td>
		  	<%end%>
		  	<% if resultAVAttributesAxelSHash.has_key?(default_channel.name) %>
		  		<td><%= resultAVAttributesAxelSHash[item[0]][2].to_s %></td>
		  		<td><%= resultAVAttributesAxelSHash[item[0]][1].to_s %></td>
		  	<%else%>
			  	<td> - </td>
			  	<td> - </td>
		  	<%end%>
		  	<td><%= resultTotalProgramInformationItemsAxelSHash[item[0]][1].to_s.first(10)  %></td>
		  	<td><%= resultTotalProgramInformationItemsAxelSHash[item[0]][2].to_s.first(10)  %></td>
	  		<td> - </td>
		<% else%>
			<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
	  		<td> - </td>
	  	<%end%>
	  	
	 <% resultTotalProgramInformationItemsAxelSHash.delete(item[0])%>
	</tr>
   <% end %>
   <% for item in resultTotalProgramInformationItemsAxelSHash %>
   		<tr  class='modo2'>
   			<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<td> - </td>
		  	<% if resultTotalProgramInformationItemsHash.has_key?(item[0]) %>
	      		<td><%= item[0] %> with <%= item[1][0].to_s %> items</td>
		  		<td><%= resultLanguageHash[item[0]][2] %> in <%= resultLanguageHash[item[0]][1].to_s%></td>
		  	<%else%>
		  		<td> - </td>
		  		<td> - </td>
		  	<%end%>
		  	<% if resultAVAttributesHash.has_key?(item[0]) %>
		  		<td><%= resultAVAttributesHash[item[0]][2].to_s %></td>
		  		<td><%= resultAVAttributesHash[item[0]][1].to_s %></td>
		  	<%else%>
			  	<td> - </td>
			  	<td> - </td>
		  	<%end%>
		  	<td><%= item[1][1].to_s.first(10)  %></td>
		  	<td><%= item[1][2].to_s.first(10)  %></td>
		  	<td> - </td>
   		</tr>
   <% end %>
</table>
